/** 
 * @Title: TopicBiz.java 
 * @Package com.zizo.autohome.emotion.dao 
 * @Description: TODO
 * @author HeYan
 * @date 2017年1月6日 下午5:34:48 
 */
package com.zizo.autohome.emotion.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.zizo.util.ConnectionPool;

/**
 * @ClassName: TopicBiz
 * @Description: TODO
 * @author HeYan
 * @date 2017年1月6日 下午5:34:48
 * 
 */
public final class TopicBiz {
	private Connection conn168 = ConnectionPool.getConnection("carvoice_biz", "192.169.0.168");
	private Connection conn198 = ConnectionPool.getConnection("carvoice_autohome", "localhost");
	private String tablename;// topic的表名

	public TopicBiz(String tablename) {
		super();
		this.tablename = tablename;
	}

	public void getTopoicData() {
		Statement st = null;
		ResultSet rs = null;
		String sql = "SELECT topic_cid,topic_mid,YEAR(topic_ptime) AS topic_yr,MONTH(topic_ptime) AS topic_mon,topic_province AS topic_prov,topic_city,topic_t1,COUNT(DISTINCT topic_sentence) AS topic_t1cnt,ROUND(AVG(topic_emotion_7),2) AS topic_t1emo FROM "
				+ tablename
				+ " WHERE topic_cid in (SELECT a_cid FROM socar_core.relate_car) GROUP BY topic_cid,topic_mid,topic_yr,topic_mon,topic_prov,topic_city,topic_t1;";
		try {
			st = conn198.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				int cid = rs.getInt(1);
				int mid = rs.getInt(2);
				int year = rs.getInt(3);
				int month = rs.getInt(4);
				String prov = rs.getString(5);
				String city = rs.getString(6);
				String t1 = rs.getString(7);
				int cnt = rs.getInt(8);
				double emo = rs.getDouble(9);
				insert(cid, mid, year, month, prov, city, t1, cnt, emo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void insert(int cid, int mid, int year, int month, String prov, String city, String t1, int cnt,
			double emo) {
		String sql = "INSERT INTO carvoice_biz.biz_topic_temp(topic_cid,topic_mid,topic_yr,topic_mon,topic_prov,topic_city,topic_t1,topic_t1cnt,topic_t1emo) values(?,?,?,?,?,?,?,?,?)";
		PreparedStatement ps = null;
		try {
			ps = conn168.prepareStatement(sql);
			ps.setInt(1, cid);
			ps.setInt(2, mid);
			ps.setInt(3, year);
			ps.setInt(4, month);
			ps.setString(5, prov);
			ps.setString(6, city);
			ps.setString(7, t1);
			ps.setInt(8, cnt);
			ps.setDouble(9, emo);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void createtable() {
		String sql = "DROP TABLE IF EXISTS biz_topic_temp";
		String sql1 = "CREATE table biz_topic_temp like biz_topic";
		Statement st = null;
		try {
			st = conn168.createStatement();
			st.executeUpdate(sql);
			st.executeUpdate(sql1);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void droptable() {
		String sql = "DROP TABLE IF EXISTS biz_topic";
		String sql1 = "Alter table biz_topic_temp rename biz_topic";
		Statement st = null;
		try {
			st = conn168.createStatement();
			st.executeUpdate(sql);
			st.executeUpdate(sql1);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void updateArea() {
		String sql = "UPDATE carvoice_biz.biz_topic AS a JOIN (SELECT cid,a_cid from socar_core.relate_car) AS b ON a.topic_cid=b.a_cid SET a.topic_cid=b.cid;";
		String sql1 = "UPDATE carvoice_biz.biz_topic AS a JOIN (SELECT mid,a_mid from socar_core.relate_model) AS b ON a.topic_mid=b.a_mid SET a.topic_mid=b.mid;";
		String sql2 = "UPDATE carvoice_biz.biz_topic AS a JOIN (SELECT cid,cname FROM socar_core.c_info) AS b ON a.topic_cid=b.cid SET a.topic_cname=b.cname;";
		String sql3 = "UPDATE carvoice_biz.biz_topic AS a JOIN (SELECT mid,mname FROM socar_core.m_info) AS b ON a.topic_mid=b.mid SET a.topic_mname=b.mname;";
		String sql4 = "UPDATE carvoice_biz.biz_topic AS a JOIN (SELECT area_city,area_class,area_region FROM carvoice_core.core_area) AS b ON a.topic_city=b.area_city SET a.topic_area=b.area_region,a.topic_tier=b.area_class;";
		Statement st = null;
		try {
			st = conn168.createStatement();
			st.executeUpdate(sql);
			st.executeUpdate(sql1);
			st.executeUpdate(sql2);
			st.executeUpdate(sql3);
			st.executeUpdate(sql4);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) {
		TopicBiz t = new TopicBiz("topic_auto_kb_1230");
		t.createtable();
		t.getTopoicData();
		t.droptable();
		t.updateArea(); 
	}
}
